﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace WikeSoft.Enterprise.AppServices
{

    /// <summary>
    /// SQL参数
    /// </summary>
    public class SqlParm
    {
        /// <summary>
        /// 列名
        /// </summary>
        public String ColumnName { get; set; }
        /// <summary>
        /// 数据库字段类型
        /// </summary>
        public SqlDbType SqlDbType { get; set; }

        /// <summary>
        /// 更新的值
        /// </summary>
        public String ColumnValue { get; set; }

    }

    /// <summary>
    /// 表主键
    /// </summary>
    public class TablePrimaryKey
    {
        /// <summary>
        /// 主键的列名
        /// </summary>
        public String ColumnName { get; set; }

        /// <summary>
        /// 数据库字段类型
        /// </summary>
        public SqlDbType SqlDbType { get; set; }
 
    }

    /// <summary>
    /// 服务
    /// </summary>
    public class BaseServices
    {
        /// <summary>
        /// 批量写入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection"></param>
        /// <param name="tableName"></param>
        /// <param name="list"></param>
        protected void BulkInsert<T>(SqlConnection connection, String tableName, IList<T> list)
        {
            if (connection.State != ConnectionState.Open)
            {
                connection.Open(); //打开Connection连接  
            }
            using (var bulkCopy = new SqlBulkCopy(connection))
            {
                bulkCopy.BatchSize = list.Count;
                bulkCopy.DestinationTableName = tableName;

                var table = ListToTable(list); 
                bulkCopy.WriteToServer(table);
            }
            if (connection.State != ConnectionState.Closed)
            {
                connection.Close(); //关闭Connection连接  
            }
        }

        /// <summary>
        /// List转DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <returns></returns>

        private DataTable ListToTable<T>(IList<T> list)
        {
            var table = new DataTable();
             
            var props = TypeDescriptor.GetProperties(typeof(T))

                .Cast<PropertyDescriptor>()
                .Where(propertyInfo => propertyInfo.PropertyType.Namespace.Equals("System"))
                .ToArray();

            foreach (var propertyInfo in props)
            {
                table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);
            }

            var values = new object[props.Length];
            foreach (var item in list)
            {
                for (var i = 0; i < values.Length; i++)
                {
                    values[i] = props[i].GetValue(item);
                }

                table.Rows.Add(values);
            }
            return table;

        }
        /// <summary>
        /// 批量更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection"></param>
        /// <param name="tableName"></param>
        /// <param name="list"></param>
        /// <param name="parmses"></param>
        /// <param name="pks"></param>
        protected void BulkUpdate<T>(SqlConnection connection, String tableName, IList<T> list,List<SqlParm> parmses,List<TablePrimaryKey> pks)
        {
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();  
            }

            var table = ListToTable(list);
            table.TableName = tableName;
            table.AcceptChanges();
          

            for (int i = 0; i < table.Rows.Count; i++)
            {
                table.Rows[i].BeginEdit();

                foreach (SqlParm batchParms in parmses)
                {
                    table.Rows[i][batchParms.ColumnName] = batchParms.ColumnValue;
                }
                table.Rows[i].EndEdit();
            }
           
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            String parms = string.Empty;
            for (int i = 0; i < parmses.Count; i++)
            {
                if (i < parmses.Count - 1)
                {
                    parms += parmses[i].ColumnName + "=@" + parmses[i].ColumnName + ",";
                }
                else
                {
                    parms += parmses[i].ColumnName + "=@" + parmses[i].ColumnName;

                }
            }

            String where = String.Empty;
            for (int i = 0; i < pks.Count; i++)
            {
                if (i < pks.Count - 1)
                {
                    where += pks[i].ColumnName + "=@" + pks[i].ColumnName + "and ";
                }
                else
                {
                    where += pks[i].ColumnName + "=@" + pks[i].ColumnName;
                }
            }

            String updateSQL =string.Format("Update {0} set {1} where {2}",tableName, parms, where) ;
            SqlCommand cmd = new SqlCommand(updateSQL, connection);

           


               // .Join(pks.Select(c=>new {}))

            int index = 0;
            for (int i = 0; i < parmses.Count; i++)
            {
                cmd.Parameters.Add(string.Format("@{0}", parmses[i].ColumnName), parmses[i].SqlDbType,index, parmses[i].ColumnName);
                index++;
            }


            for (int i = 0; i < pks.Count; i++)
            {
                cmd.Parameters.Add(string.Format("@{0}", pks[i].ColumnName), pks[i].SqlDbType, index, pks[i].ColumnName);
                index++;
            }
            sqlDataAdapter.UpdateCommand = cmd;
            sqlDataAdapter.Update(table); 
            if (connection.State != ConnectionState.Closed)
            {
                connection.Close(); //关闭Connection连接  
            }
        }
    }
}
